//
//  DataCenter.m
//  Karaoke
//
//  Created by Ka Leung Ke on 11-10-6.
//  Copyright 2011年 __MyCompanyName__. All rights reserved.

#import "DataCenter.h"
#import "OkSingerObject.h"
#import "OpenUDID.h"
#import "SingerTypeObject.h"
#import "SongTypeObject.h"
#import "LanTypeObject.h"


// select * from test where a > date('now', '-1 month', 'localtime');
@implementation DataCenter
@synthesize currentBillListArray;
@synthesize clerkNo, priceId, roomNo, printId, clerkName, roomType, canOrder,UDID;
@synthesize isBoundByRoom,isBoundByClerk;
static DataCenter *_sharedInstance;

+ (DataCenter *)sharedInstance
{
    if (!_sharedInstance)
    {
        _sharedInstance = [[DataCenter alloc] init];
    }

    return _sharedInstance;
}

- (id)init
{
    self = [super init];

    if (self)
    {
        currentBillListArray = nil;
        // Initialization code here.
        arrayFavor = [[NSMutableArray alloc] init];
        self.priceId = @"1";
        self.UDID = [OpenUDID value];
        isBoundByClerk = [[NSUserDefaults standardUserDefaults] boolForKey:@"BoundType"];
        
    }

    return self;
}

- (void)addIndexToDb
{
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_BILLBOARDORDER on OK_SONG(L_CODE, S_WORDCOUNT ASC, S_INPUTCODE ASC, S_COUNT DESC);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_BILLBOARDORDER2 on OK_SONG(S_COUNT DESC);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_DEFAULTORDER on OK_SONG(L_CODE, S_WORDCOUNT ASC, S_INPUTCODE ASC);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_LCODE on OK_SONG(L_CODE);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_SRNUM ON OK_SONG(SR_NUM);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_SRNUM2 ON OK_SONG(SR_NUM2);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_SWORDCOUNT on OK_SONG(S_WORDCOUNT ASC);"];
//    [db executeUpdate:@"CREATE INDEX INDEX_OKSONG_SWORDCOUNT2 on OK_SONG(L_CODE, S_WORDCOUNT ASC);"];
//    [db executeQuery:@"CREATE INDEX INDEX_OKSONGER_SRTCODE on OK_SONGER(SRT_CODE);"];
//    [db executeQuery:@"CREATE INDEX INDEX_OKSONGER_SRINPUTCODE on OK_SONGER(SR_INPUTCODE);"];
//    [db executeQuery:@"CREATE INDEX INDEX_OKSONG_TYPE on OK_SONG(ST_CODE, S_WORDCOUNT ASC, S_INPUTCODE ASC, S_COUNT DESC);"];
//    // [db executeQuery:@"CREATE INDEX TEST2 on OK_SONG(S_COUNT DESC);"];
//    NSLog(@"create index Last error msg: %@", [db lastErrorMessage]);
}

- (void)loadDataAfterReady
{
    arrayAllSinger = nil;
    arraySingerType = nil;
    arraySongType = nil;
    arrayLanType = nil;

    [self getAllSinger];
    [self getSingerType];
    [self getSongType];
    [self getLanType];
}

- (void)initDb
{
    //    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    //    NSString *documentsDirectory = [paths objectAtIndex:0];
    //    NSString *pathWithFolder = [documentsDirectory stringByAppendingPathComponent:@"db"];
    //    NSString *filePath = [pathWithFolder stringByAppendingPathComponent:tempDbName];

    NSArray     *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString    *documentsDirectory = [paths objectAtIndex:0];
    NSString    *pathWithFolder = [documentsDirectory stringByAppendingPathComponent:@"db"];
    NSString    *filePath = [pathWithFolder stringByAppendingPathComponent:@"song.db"];

    // NSString *filePath = [[NSBundle mainBundle] pathForResource:@"song" ofType:@"db"];

    db = [FMDatabase databaseWithPath:filePath];

    if (![db open])
    {
        NSLog(@"Could not open db.");
    }

    [db retain];
}

- (NSMutableArray *)getAllSong:(int)tempCondition
{
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithString:@"select * from OK_SONG"];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    [tempSqlString appendFormat:@" LIMIT %d,18;", tempCondition];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (NSMutableArray *)getSongWithInputCode:(NSString *)inputCode tempCondition:(NSString *)tempCondition
{
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
    FMResultSet     *rs = [db executeQuery:@"select * from OK_SONG where S_INPUTCODE LIKE ? limit 0,300 ", [NSString stringWithFormat:@"%@%%", inputCode]];
    SongObject      *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
    }

    return [tempArray autorelease];
}

- (NSMutableArray *)getSingerListWithCondition:(NSMutableArray *)tempConditionArray withRange:(NSRange)tempRange
{
    NSLog(@"start load song list with condition");
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithString:@"select * from OK_SONGER"];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (!(([tempConditionArray count] == 2) && [[tempConditionArray objectAtIndex:0] isEqualToString:@""]))
    {
        [tempSqlString appendString:@" where "];

        for (int i = 0; i < [tempConditionArray count]; i++)
        {
            [tempSqlString appendString:[tempConditionArray objectAtIndex:i]];
        }
    }

    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (int)getTotalRowNumOfSingerListWithCondition:(NSMutableArray *)tempConditionArray
{
    NSMutableString *tempSqlString = [[[NSMutableString alloc] initWithString:@"select count(*) from OK_SONGER"] autorelease];

    if (!(([tempConditionArray count] == 2) && [[tempConditionArray objectAtIndex:0] isEqualToString:@""]))
    {
        [tempSqlString appendString:@" where "];

        for (int i = 0; i < [tempConditionArray count]; i++)
        {
            [tempSqlString appendString:[tempConditionArray objectAtIndex:i]];
        }
    }

    [tempSqlString appendString:@";"]; // @"ORDER BY S_WORDCOUNT LIMIT 200;"];
    FMResultSet *rs = [db executeQuery:tempSqlString];

    if ([rs next])
    {
        return [rs intForColumnIndex:0];
    }

    return 0;
}

// - (int) getTotalRowNumOfSongListWithCondition:(NSMutableArray *)tempConditionArray
// {
//    NSMutableString * tempSqlString = [[NSMutableString alloc] initWithString:@"select count(*) from OK_SONG"];
//
//    if (!(([tempConditionArray count] == 2) && [[tempConditionArray objectAtIndex:0] isEqualToString:@""]))
//    {
//        [tempSqlString appendString:@" where "];
//
//        for (int i = 0; i < [tempConditionArray count]; i++)
//        {
//            [tempSqlString appendString:[tempConditionArray objectAtIndex:i]];
//        }
//    }
//
//    [tempSqlString appendString:@";"]; // @"ORDER BY S_WORDCOUNT LIMIT 200;"];
//    FMResultSet * rs = [db executeQuery:tempSqlString];
//
//    if ([rs next])
//    {
//        return [rs intForColumnIndex:0];
//    }
//
//    return 0;
// }

- (int)getTotalRowNumOfSongListWithCondition:(NSArray *)tempCondition
{
    NSMutableString *tempSqlString = [[[NSMutableString alloc] initWithString:@"select count(*) from OK_SONG"] autorelease];

    if (tempCondition.count > 0)
    {
        [tempSqlString appendString:@" where "];

        for (int i = 0; i < tempCondition.count; i++)
        {
            if (i > 0)
            {
                [tempSqlString appendString:@" AND "];
            }

            [tempSqlString appendFormat:@" %@ ", [tempCondition objectAtIndex:i]];
        }
    }

    [tempSqlString appendString:@";"]; // @"ORDER BY S_WORDCOUNT LIMIT 200;"];
    NSLog(@"%@", tempSqlString);
    FMResultSet *rs = [db executeQuery:tempSqlString];

    if ([rs next])
    {
        return [rs intForColumnIndex:0];
    }

    return 0;
}

- (NSMutableArray *)getSongListWithCondition:(NSArray *)tempCondition withRange:(NSRange)tempRange
{
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithString:@"select S_Code,S_NAME,SR_Num,SR_Num2,S_Singer1,S_Singer2,L_CODE,S_CopyDest from OK_SONG"];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (tempCondition.count > 0)
    {
        [tempSqlString appendString:@" where "];

        for (int i = 0; i < tempCondition.count; i++)
        {
            if (i > 0)
            {
                [tempSqlString appendString:@" AND "];
            }

            [tempSqlString appendFormat:@" %@ ", [tempCondition objectAtIndex:i]];
        }
    }

    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (NSMutableArray *)getSongListWithConditionss:(NSMutableArray *)tempConditionArray withRange:(NSRange)tempRange
{
    NSLog(@"start load song list with condition");
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithString:@"select * from OK_SONG"];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (!(([tempConditionArray count] == 2) && [[tempConditionArray objectAtIndex:0] isEqualToString:@""]))
    {
        [tempSqlString appendString:@" where "];

        for (int i = 0; i < [tempConditionArray count]; i++)
        {
            [tempSqlString appendString:[tempConditionArray objectAtIndex:i]];
        }
    }

    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (void)AddSongToFavorWithSongId:(NSString *)tempSongId
{
    if (arrayFavor != nil)
    {
        for (int i = 0; i < [arrayFavor count]; i++)
        {
            NSString *tempSongIdInArray = [arrayFavor objectAtIndex:i];

            if ([tempSongId isEqualToString:tempSongIdInArray])
            {
                return;
            }
        }
    }

    [arrayFavor addObject:tempSongId];
}

- (void)delSongFromFavorWithSongId:(NSString *)tempSongId
{
    if (arrayFavor != nil)
    {
        for (int i = 0; i < [arrayFavor count]; i++)
        {
            NSString *tempSongIdInArray = [arrayFavor objectAtIndex:i];

            if ([tempSongId isEqualToString:tempSongIdInArray])
            {
                [arrayFavor removeObjectAtIndex:i];
                return;
            }
        }
    }
}

- (NSMutableArray *)getFavorArray
{
    NSMutableArray *tempArray = [[NSMutableArray alloc] init];

    for (int i = 0; i < [arrayFavor count]; i++)
    {
        [tempArray addObject:[self getSongObjectWithSongId:[arrayFavor objectAtIndex:i]]];
    }

    return [tempArray autorelease];
}

- (SongObject *)getSongObjectWithSongId:(NSString *)tempSongId
{
    FMResultSet *rs = [db executeQuery:@"select * from OK_SONG where S_Code = ?", tempSongId];

    SongObject *tempSongObject = nil;

    if ([rs next])
    {
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }
    }

    return [tempSongObject autorelease];
}

- (NSMutableArray *)getSongListWithSingerId:(NSString *)tempSingerId:(NSString *)tempCondition
{
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithFormat:@"select * from OK_SONG where  (SR_NUM == '%@' OR SR_NUM2 == '%@' ) ", tempSingerId, tempSingerId];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (tempCondition.length > 0)
    {
        [tempSqlString appendFormat:@" AND %@ ", tempCondition];
    }

    //    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (NSMutableArray *)getSongListWithSingerId:(NSString *)tempSingerId
{
    NSMutableArray *tempArray = [[NSMutableArray alloc] init];

    FMResultSet *rs = [db executeQuery:@"select * from OK_SONG where  (SR_NUM == ? OR SR_NUM2 == ?) ", tempSingerId, tempSingerId];

    NSLog(@"going to find the songs with singerId: %@", tempSingerId);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
    }

    return [tempArray autorelease];
}

- (NSMutableArray *)getLanType
{
    //    if (arrayLanType == nil) {
    //        NSMutableArray *tempArray = [[NSMutableArray alloc] init];
    //        FMResultSet *rs = [db executeQuery:@"select * from OK_LANGUAGE;"];
    //
    //        LanTypeObject *tempObject = nil;
    //        while([rs next])
    //        {
    //            tempObject = [[LanTypeObject alloc] init];
    //            tempObject.typeCode = [rs intForColumn:@"L_CODE"];
    //            tempObject.typeName = [rs stringForColumn:@"L_NAME"];
    //            NSLog(@"get singer type with name: %@", tempObject.typeName);
    //            [tempArray addObject:tempObject];
    //            [tempObject release];
    //        }
    //
    //        arrayLanType = tempArray;
    //    }

    if (arrayLanType == nil)
    {
        NSMutableArray *tempArray = [[NSMutableArray alloc] init];

        LanTypeObject *tempObject[10];

        for (int i = 0; i < 10; i++)
        {
            tempObject[i] = [[LanTypeObject alloc] init];
            tempObject[i].typeCode = i;
        }

        tempObject[0].typeName = @"國語";
        tempObject[1].typeName = @"粵語";
        tempObject[2].typeName = @"日語";
        tempObject[3].typeName = @"客家";
        tempObject[4].typeName = @"馬來";
        tempObject[5].typeName = @"英語";
        tempObject[6].typeName = @"韓語";
        tempObject[7].typeName = @"潮州";
        tempObject[8].typeName = @"閩語";
        tempObject[9].typeName = @"其他";

        for (int i = 0; i < 10; i++)
        {
            [tempArray addObject:tempObject[i]];
        }

        arrayLanType = tempArray;
    }

    return arrayLanType;
}

- (NSMutableArray *)getSongType
{
    // CREATE TABLE [OK_SONGTYPE] ( [ST_CODE] smallint,[ST_NAME] varchar , Primary Key (ST_CODE) , Unique (ST_CODE));
    //    if (arraySongType == nil) {
    //        NSMutableArray *tempArray = [[NSMutableArray alloc] init];
    //        FMResultSet *rs = [db executeQuery:@"select * from OK_SONGTYPE;"];
    //
    //        SongTypeObject *tempObject = nil;
    //        while([rs next])
    //        {
    //            tempObject = [[SongTypeObject alloc] init];
    //            tempObject.typeCode = [rs intForColumn:@"ST_CODE"];
    //            tempObject.typeName = [rs stringForColumn:@"ST_NAME"];
    //            //NSLog(@"get singer type with name: %@", tempObject.typeName);
    //            [tempArray addObject:tempObject];
    //            [tempObject release];
    //        }
    //
    //        arraySongType = tempArray;
    //    }
    if (arraySongType == nil)
    {
        NSMutableArray *tempArray = [[NSMutableArray alloc] init];

        SongTypeObject *tempObject[21];

        for (int i = 0; i < 21; i++)
        {
            tempObject[i] = [[SongTypeObject alloc] init];
            tempObject[i].typeCode = i;
        }

        //        对唱: 10
        //        合唱：11
        //        摇滚：12
        //        草原：20
        //        生日：13

        //        儿童：23
        //        怀旧：21
        //        民歌：14
        //        慢摇：19
        //        革命：15

        //        迪曲：17
        //        其他舞曲：18、61、62、63、64
        //        电影插曲：24
        //        粤剧：74
        //        豫剧：75

        //        京剧：70
        //        沪剧：71
        //        越剧：73
        //        黄梅戏：72
        //        评剧：76

        //        其他戏曲：16

        tempObject[0].typeName = @"豫剧";
        tempObject[1].typeName = @"京剧";
        tempObject[2].typeName = @"怀旧";
        tempObject[3].typeName = @"民歌";
        tempObject[4].typeName = @"迪曲";

        tempObject[5].typeName = @"越剧";
        tempObject[6].typeName = @"合唱";
        tempObject[7].typeName = @"粤剧";
        tempObject[8].typeName = @"摇滚";
        tempObject[9].typeName = @"草原";

        tempObject[10].typeName = @"生日";
        tempObject[11].typeName = @"黄梅戏";
        tempObject[12].typeName = @"评剧";
        tempObject[13].typeName = @"沪剧";
        tempObject[14].typeName = @"其他戏曲";

        tempObject[15].typeName = @"评剧";
        tempObject[16].typeName = @"革命";
        tempObject[17].typeName = @"儿童";
        tempObject[18].typeName = @"慢摇";
        tempObject[19].typeName = @"其他舞曲";

        tempObject[20].typeName = @"对唱";

        for (int i = 0; i < 21; i++)
        {
            [tempArray addObject:tempObject[i]];
        }

        arraySongType = tempArray;
    }

    return arraySongType;
}

- (NSMutableArray *)getSingerType
{
    // CREATE TABLE [OK_SONGERTYPE] ( [SRT_CODE] smallint,[SRT_NAME] varchar , Primary Key (SRT_CODE) , Unique (SRT_CODE));
    //    if (arraySingerType == nil) {
    //        NSMutableArray *tempArray = [[NSMutableArray alloc] init];
    //        FMResultSet *rs = [db executeQuery:@"select * from OK_SONGERTYPE;"];
    //
    //        SingerTypeObject *tempObject = nil;
    //        while([rs next])
    //        {
    //            tempObject = [[SingerTypeObject alloc] init];
    //            tempObject.typeCode = [rs intForColumn:@"SRT_CODE"];
    //            tempObject.typeName = [rs stringForColumn:@"SRT_NAME"];
    //            //NSLog(@"get singer type with name: %@", tempObject.typeName);
    //            [tempArray addObject:tempObject];
    //            [tempObject release];
    //        }
    //
    //        arraySingerType = tempArray;
    //    }
    //

    if (arraySingerType == nil)
    {
        NSMutableArray *tempArray = [[NSMutableArray alloc] init];

        SingerTypeObject *tempObject[10];

        for (int i = 0; i < 10; i++)
        {
            tempObject[i] = [[SingerTypeObject alloc] init];
            tempObject[i].typeCode = i;
        }

        tempObject[0].typeName = @"全部歌星";
        tempObject[1].typeName = @"大陸女歌星";
        tempObject[2].typeName = @"港臺女歌星";
        tempObject[3].typeName = @"歐美歌星";
        tempObject[4].typeName = @"韓國歌星";
        tempObject[5].typeName = @"大陸男歌星";
        tempObject[6].typeName = @"港臺男歌星";
        tempObject[7].typeName = @"華語組合";
        tempObject[8].typeName = @"日本歌星";
        tempObject[9].typeName = @"南亞其他";

        for (int i = 0; i < 10; i++)
        {
            [tempArray addObject:tempObject[i]];
        }

        arraySingerType = tempArray;
    }

    return arraySingerType;
}

- (NSMutableArray *)getSingerOfType:(int)tempId
{
    // NSLog(@"getSingerTypeFor: %i", tempId);

    //    tempObject[0].typeName = @"全部歌星";
    //    tempObject[1].typeName = @"大陸女歌星";
    //    tempObject[2].typeName = @"港臺女歌星";
    //    tempObject[3].typeName = @"歐美歌星";
    //    tempObject[4].typeName = @"韓國歌星";
    //    tempObject[5].typeName = @"大陸男歌星";
    //    tempObject[6].typeName = @"港臺男歌星";
    //    tempObject[7].typeName = @"華語組合";
    //    tempObject[8].typeName = @"日本歌星";
    //    tempObject[9].typeName = @"南亞其他";

    //    1|大陆
    //    2|台湾
    //    3|香港
    //    4|日本
    //    5|韩国
    //    6|欧美
    //    7|新加坡
    //    8|马来西亚
    //    9|印尼
    //    10|泰国
    //    11|越南
    //    12|菲律宾
    //    13|印度
    //    14|缅甸
    //    15|其他

    //    华语是包括：大陆、香港、台湾、新加坡。
    //    南亚除大陆、香港、台湾、新家坡的其他南亚国家 算在 南亚其他里
    NSString *tempSql;

    if (tempId == 1)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_Sex = 1 AND SRT_CODE = 1";
    }
    else
    if (tempId == 2)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_Sex = 1 AND (SRT_CODE = 2 OR SRT_CODE = 3)";
    }
    else
    if (tempId == 3)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SRT_CODE = 6";
    }
    else
    if (tempId == 4)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SRT_CODE = 5";
    }
    else
    if (tempId == 5)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_Sex = 0 AND SRT_CODE = 1";
    }
    else
    if (tempId == 6)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_Sex = 0 AND (SRT_CODE = 2 OR SRT_CODE = 3)";
    }
    else
    if (tempId == 7)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_Sex = 2 AND (SRT_CODE = 2 OR SRT_CODE = 3 OR SRT_CODE = 1 OR SRT_CODE = 7)";
    }
    else
    if (tempId == 8)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND  SRT_CODE = 4";
    }
    else
    if (tempId == 9)
    {
        tempSql = @"select * from OK_SONGER where SR_IFSHOW = 1 AND (SRT_CODE <> 2 AND SRT_CODE <> 3 AND SRT_CODE <> 1 AND SRT_CODE <> 7)";
    }
    else
    {
        return arrayAllSinger;
    }

    NSLog(@"run sql: %@", tempSql);
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
    FMResultSet     *rs = [db executeQuery:tempSql];

    OkSingerObject *tempSingerObject = nil;

    while ([rs next])
    {
        tempSingerObject = [[OkSingerObject alloc] init];
        tempSingerObject.singerId = [rs stringForColumn:@"SR_NUM"];
        tempSingerObject.singerName = [rs stringForColumn:@"SR_NAME"];
        tempSingerObject.singerPhotoNum = [rs stringForColumn:@"SR_PHOTONO"];
        tempSingerObject.singerWordCount = [rs stringForColumn:@"SR_WORDCOUNT"];
        tempSingerObject.singerInputCode = [rs stringForColumn:@"SR_INPUTCODE"];
        tempSingerObject.singerSex = [rs stringForColumn:@"SR_SEX"];
        tempSingerObject.singerSexName = [rs stringForColumn:@"SR_SEXNAME"];
        tempSingerObject.singerOrder = [rs stringForColumn:@"SR_ORDER"];
        tempSingerObject.singerCode = [rs stringForColumn:@"SRT_CODE"];
        [tempArray addObject:tempSingerObject];
        [tempSingerObject release];
    }

    return [tempArray autorelease];
}

- (NSMutableArray *)getSingerWithName:(NSString *)tempName
{
    // FMResultSet *rs = [db executeQuery:@"select * from food_list where foodPY LIKE ? OR foodName LIKE ?;", [NSString stringWithFormat:@"%%%@%%", tempSearchWord], [NSString stringWithFormat:@"%%%@%%", tempSearchWord]];

    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
    FMResultSet     *rs = [db executeQuery:@"select * from OK_SONGER where SR_IFSHOW = 1 AND SR_NAME LIKE ?;", [NSString stringWithFormat:@"%%%@%%", tempName]];

    OkSingerObject *tempSingerObject = nil;

    while ([rs next])
    {
        tempSingerObject = [[OkSingerObject alloc] init];
        tempSingerObject.singerId = [rs stringForColumn:@"SR_NUM"];
        tempSingerObject.singerName = [rs stringForColumn:@"SR_NAME"];
        tempSingerObject.singerPhotoNum = [rs stringForColumn:@"SR_PHOTONO"];
        tempSingerObject.singerWordCount = [rs stringForColumn:@"SR_WORDCOUNT"];
        tempSingerObject.singerInputCode = [rs stringForColumn:@"SR_INPUTCODE"];
        tempSingerObject.singerSex = [rs stringForColumn:@"SR_SEX"];
        tempSingerObject.singerSexName = [rs stringForColumn:@"SR_SEXNAME"];
        tempSingerObject.singerOrder = [rs stringForColumn:@"SR_ORDER"];
        tempSingerObject.singerCode = [rs stringForColumn:@"SRT_CODE"];
        [tempArray addObject:tempSingerObject];
        [tempSingerObject release];
    }

    return [tempArray autorelease];
}

- (NSMutableArray *)getSingerWithPy:(NSString *)tempPy
{
    // FMResultSet *rs = [db executeQuery:@"select * from food_list where foodPY LIKE ? OR foodName LIKE ?;", [NSString stringWithFormat:@"%%%@%%", tempSearchWord], [NSString stringWithFormat:@"%%%@%%", tempSearchWord]];

    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
    FMResultSet     *rs = [db executeQuery:@"select * from OK_SONGER where  SR_IFSHOW = 1 AND SR_INPUTCODE LIKE ?;", [NSString stringWithFormat:@"%@%%", tempPy]];

    OkSingerObject *tempSingerObject = nil;

    while ([rs next])
    {
        tempSingerObject = [[OkSingerObject alloc] init];
        tempSingerObject.singerId = [rs stringForColumn:@"SR_NUM"];
        tempSingerObject.singerName = [rs stringForColumn:@"SR_NAME"];
        tempSingerObject.singerPhotoNum = [rs stringForColumn:@"SR_PHOTONO"];
        tempSingerObject.singerWordCount = [rs stringForColumn:@"SR_WORDCOUNT"];
        tempSingerObject.singerInputCode = [rs stringForColumn:@"SR_INPUTCODE"];
        tempSingerObject.singerSex = [rs stringForColumn:@"SR_SEX"];
        tempSingerObject.singerSexName = [rs stringForColumn:@"SR_SEXNAME"];
        tempSingerObject.singerOrder = [rs stringForColumn:@"SR_ORDER"];
        tempSingerObject.singerCode = [rs stringForColumn:@"SRT_CODE"];
        [tempArray addObject:tempSingerObject];
        [tempSingerObject release];
    }

    return [tempArray autorelease];
}

- (NSMutableArray *)getAllSinger
{
    // CREATE TABLE [OK_SONGER] ( [SR_NUM] varchar,[SR_NAME] varchar,[SR_PHOTONO] varchar,[SR_WORDCOUNT] smallint,[SR_INPUTCODE] varchar,[SR_IFSHOW] varchar,[SR_SEX] smallint,[SR_SEXNAME] varchar,[SR_ORDER] smallint,[SRT_CODE] smallint , Primary Key (SR_NUM) , Unique (SR_NUM));

    // @synthesize singerId, singerName, singerPhotoNum, singerWordCount, singerInputCode, singerSex, singerSexName, singerOrder, singerCode;

    if (arrayAllSinger == nil)
    {
        NSLog(@"start to load all singer data");
        dictForSingerType = [[NSMutableDictionary alloc] init];
        NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
        FMResultSet     *rs = [db executeQuery:@"select * from OK_SONGER where SR_IFSHOW = 1;"];

        OkSingerObject *tempSingerObject = nil;

        while ([rs next])
        {
            tempSingerObject = [[OkSingerObject alloc] init];
            tempSingerObject.singerId = [rs stringForColumn:@"SR_NUM"];
            tempSingerObject.singerName = [rs stringForColumn:@"SR_NAME"];
            tempSingerObject.singerPhotoNum = [rs stringForColumn:@"SR_PHOTONO"];
            tempSingerObject.singerWordCount = [rs stringForColumn:@"SR_WORDCOUNT"];
            tempSingerObject.singerInputCode = [rs stringForColumn:@"SR_INPUTCODE"];
            tempSingerObject.singerSex = [rs stringForColumn:@"SR_SEX"];
            tempSingerObject.singerSexName = [rs stringForColumn:@"SR_SEXNAME"];
            tempSingerObject.singerOrder = [rs stringForColumn:@"SR_ORDER"];
            tempSingerObject.singerCode = [rs stringForColumn:@"SRT_CODE"];
            [tempArray addObject:tempSingerObject];
            [tempSingerObject release];

            // Create dict for singer type
            NSMutableArray *tempSingerForType = [dictForSingerType objectForKey:tempSingerObject.singerCode];

            if (tempSingerForType == nil)
            {
                tempSingerForType = [[NSMutableArray alloc] init];
                [dictForSingerType setObject:tempSingerForType forKey:tempSingerObject.singerCode];
            }

            [tempSingerForType addObject:tempSingerObject];
        }

        NSLog(@"finish loading all");

        arrayAllSinger = tempArray;
    }

    return arrayAllSinger;
}

- (NSMutableArray *)getSongWithWordCount:(int)count
{
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];
    NSString        *worldCount = [NSString stringWithFormat:@"%d", count];
    FMResultSet     *rs;

    if (count > 9)
    {
        rs = [db executeQuery:@"select * from OK_SONG where S_WORDCOUNT > 9 "];
    }
    else
    {
        rs = [db executeQuery:@"select * from OK_SONG where S_WORDCOUNT = ? ", worldCount];
    }

    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
    }

    return [tempArray autorelease];
}

- (void)forwardNoticeCode:(NSString *)tempCode detail:(NSString *)tempDetail
{
    NSDictionary *userInfo = [NSDictionary dictionaryWithObjectsAndKeys:
        tempCode, @"code",
        tempDetail, @"detail",
        nil];

    [[NSNotificationCenter defaultCenter] postNotificationName:@"getDataFromCenter" object:self userInfo:userInfo];
}

- (NSMutableArray *)getSongWithLanguage:(int)languageId:(NSString *)tempCondition:(NSRange)tempRange
{
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithFormat:@"select * from OK_SONG where  L_CODE == %d ", languageId];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (tempCondition.length > 0)
    {
        [tempSqlString appendFormat:@" AND %@ ", tempCondition];
    }

    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

- (NSMutableArray *)getSongListWithType:(int)typeId:(NSString *)tempCondition:(NSRange)tempRange
{
    NSMutableString *tempSqlString = [[NSMutableString alloc] initWithFormat:@"select * from OK_SONG where  ST_CODE == %d ", typeId];
    NSMutableArray  *tempArray = [[NSMutableArray alloc] init];

    if (tempCondition.length > 0)
    {
        [tempSqlString appendFormat:@" AND %@ ", tempCondition];
    }

    [tempSqlString appendFormat:@" LIMIT %i,%i;", tempRange.location, tempRange.length];

    FMResultSet *rs = [db executeQuery:tempSqlString];

    NSLog(@"Going to get the song list with sql: %@", tempSqlString);
    SongObject *tempSongObject = nil;

    while ([rs next])
    {
        NSLog(@"create object");

        // NSLog(@"finish create object");
        tempSongObject = [[SongObject alloc] init];
        tempSongObject.songCode = [rs stringForColumn:@"S_Code"];
        tempSongObject.songName = [rs stringForColumn:@"S_NAME"];
        // NSLog(@"find a song named: %@", tempSongObject.songName);
        tempSongObject.singerId1 = [rs stringForColumn:@"SR_Num"];
        tempSongObject.singerId2 = [rs stringForColumn:@"SR_Num2"];
        tempSongObject.singerName1 = [rs stringForColumn:@"S_Singer1"];
        tempSongObject.singerName2 = [rs stringForColumn:@"S_Singer2"];
        tempSongObject.language = [rs stringForColumn:@"L_CODE"];

        if (tempSongObject.singerName2 == NULL)
        {
            tempSongObject.singerName2 = @"";
        }

        tempSongObject.songSource = [rs stringForColumn:@"S_CopyDest"];

        if (tempSongObject.songSource == NULL)
        {
            tempSongObject.songSource = @"";
        }

        [tempArray addObject:tempSongObject];
        [tempSongObject release];
        // NSLog(@"object prepare finish");
    }

    [tempSqlString release];
    NSLog(@"finish load song list with condition");
    return [tempArray autorelease];
}

@end